﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using CNCSoft.EData;
using DevExpress.XtraEditors;
using System.Data.OleDb;
using Common;

namespace CNCSoft.ErpApps.TMS.myclass
{
    class CommonClass
    {
        private SqlConnection conn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
        DataSet ds = new DataSet();
        private int[] selectedRow;
        private string[] selectedValue;
        private string[] selectedAllValue;

        public string[] SelectedValue
        {
            get { return selectedValue; }
            set { selectedValue = value; }
        }

        public int[] SelectedRow
        {
            get { return selectedRow; }
            set { selectedRow = value; }
        }

        public CommonClass() { }

        #region coding
        /*
         * Load to ComboBox
         */
        public void ExecuteDataAdapter(string sql, SqlConnection conn, DataSet ds, string tableName, string value, string member, System.Windows.Forms.ComboBox cb)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, tableName);
                
                cb.DataSource = ds.Tables[tableName];
                cb.DisplayMember = member;
                cb.ValueMember = value;
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        /*
         * Load to SearchLookUpEdit
         */
        public void ExecuteDataAdapter(string sql, SqlConnection conn, DataSet ds, string tableName, string value, string member, SearchLookUpEdit search)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, tableName);
                search.Properties.DataSource = ds.Tables[tableName];
                search.Properties.DisplayMember = member;
                search.Properties.ValueMember = value;
                
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message);
            }
        }

        
        /*
         * Load to GridControl
         */
        public void ExecDataAdapter(string sql, SqlConnection conn, DataSet ds, string tableName, GridControl grid)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, tableName);
                grid.DataSource = ds.Tables[tableName];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        /*
         * Return DataSet
         */
        public DataSet ExecDataReturnDataSet(string sql, SqlConnection conn)
        {
            DataSet dsLocal = new DataSet();
            try
            {             
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(dsLocal);
                return dsLocal;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return dsLocal;
            }
        }
        
        /*
         * Get Value At Specific cell of column 0 of gridControl
         */
        public string GetCellValue(GridView grv, int indexRow, string strField)
        {
            if (strField == null || strField == "")
            {
                try
                {
                    strField = grv.Columns[0].FieldName;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

            try
            {
                return grv.GetRowCellValue(indexRow, strField).ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        /*
         * Get Value At Specific cell of gridControl
         */
        public string GetCellValue(GridView grv, int indexRow, int indexColumn, string strField)
        {
            try
            {
                strField = grv.Columns[indexColumn].FieldName;
                return grv.GetRowCellValue(indexRow, strField).ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        /*
         * Get string all ids ('id1', 'id2', ...., 'idn') of choosed rows in gridControl
         */
        public string Choose(GridView grv, string strField)
        {
            try
            {
                string s = "";
                selectedRow = null;
                SelectedRow = grv.GetSelectedRows();
                for (int i = 0; i < selectedRow.Length; i++)
                {
                    if (i < selectedRow.Length - 1)
                    {
                        s += "'" + GetCellValue(grv, selectedRow[i], strField) + "',";
                    }
                    else
                    {
                        s += "'" + GetCellValue(grv, selectedRow[i], strField) + "'";
                    }
                }
                return "(" + s + ")";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        /*
         * Get string all ids ('id1', 'id2', ...., 'idn') of all rows in gridControl
         */
        public string ChooseAll(GridView grv, string strField)
        {
            try
            {
                string s = "";
                int selectedall = grv.RowCount;

                for (int i = 0; i < selectedall; i++)
                {
                    if (i < selectedall - 1)
                    {
                        s += "'" + GetCellValue(grv, i, strField) + "'" + ",";
                    }
                    else
                    {
                        s += "'" + GetCellValue(grv, i, strField) + "'";
                    }
                }
                return "(" + s + ")";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        /*
         * Get values of selected rows
         */
        public string[] ChoosedValue(GridView grv, string strField)
        {            
            selectedRow = null;
            selectedRow = grv.GetSelectedRows();
            selectedValue = new string[selectedRow.Length];
            for (int i = 0; i < selectedRow.Length; i++)
            {
                SelectedValue[i] = GetCellValue(grv, selectedRow[i], strField);
            }
            return SelectedValue;
        }

        /*
         * Get all values of grid
         */
        public string[] ChooseAllValue(GridView grv, string strField)
        {
            try
            {
                string s = "";
                int selectedall = grv.RowCount;
                selectedAllValue = new string[selectedall];

                for (int i = 0; i < selectedall; i++)
                {
                    selectedAllValue[i] = GetCellValue(grv, i, strField);
                }
                return selectedAllValue;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        /*
         * Copy data from gridView to datatable
         */
        public DataTable GridToTable(GridView grv)
        {
            try
            {
                DataView dv = (DataView)grv.DataSource;
                dv.Table.DataSet.Tables[0].TableName = "TableStudentFromGrid";
                return dv.Table.DataSet.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        /*
         * Insert data from DataTable to database
         */
        public void DataTableToDatBase(SqlConnection conn, string sql, GridView grv)
        {
            try
            {
                int i = 0;
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                DataHelper.buildDataCommands(ref da);
                DataTable dt = GridToTable(grv);
                //DataSet ds = new DataSet();
                //DataTable dt = ds.Tables["abc"].GetChanges(DataRowState.Added);
                if (dt.GetChanges(DataRowState.Added) != null)
                    i = da.Update(dt);
                if (i > 0)
                    MessageBox.Show("Thành công !!");
                else
                    MessageBox.Show("Thất bại !!!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void LoadDataToGrid(string sql, string tablename, GridControl gridcontrol)
        {
            try
            {
                if (ds.Tables[tablename] != null)
                    ds.Tables[tablename].Clear();
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, tablename);
                gridcontrol.DataSource = ds.Tables[tablename];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #region "Do with excel file"
        /*
         * Browse file excel
         */
        public string BrowseFileExcel(string extend)
        {
            try
            {
                OpenFileDialog opendialog = new OpenFileDialog();
                opendialog.Filter = extend;
                if (opendialog.ShowDialog() == DialogResult.OK)
                {
                    return opendialog.FileName;
                }
                else
                    return "Ko thể lấy Path";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return ex.Message;
            }
        }

        /*
         * ConnectionString to connect file excel
         */
        public string ConnectionStringToConnectExcel(string fPath)
        {
            try
            {
                string strconn = String.Empty;
                string[] splitdot = fPath.Split(new char[1] { '.' });
                string dot = splitdot[splitdot.Length - 1].ToLower();
                if (dot == "xls")
                {
                    //tao chuoi ket noi voi excel 2003
                    strconn = @"Provider = Microsoft.Jet.OLEDB.4.0; Extended Properties = Excel 8.0; Data Source = " + fPath;
                }
                else if (dot == "xlsx")
                {
                    //tao chuoi ket noi voi excel 2007
                    strconn = @"Provider = Microsoft.ACE.OLEDB.12.0; Extended Properties = Excel 8.0; Data Source = " + fPath;
                }
                return strconn;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return ex.Message;
            }
        }

        /*This valid method is mainly used to check where the
            *null values are contained in the Excel Sheet and
            *replacing them with ""
         */
        public string Valid(OleDbDataReader myreader, int stVal)
        {
            try
            {
                object val = myreader[stVal];
                if (val != DBNull.Value)
                    return val.ToString();
                else
                    return Convert.ToString("");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return ex.Message;
            }
        }
        #endregion

        #endregion
    }
}
